/**
 * Auto gen via dbpojo-gen.
 */
package com.us.usws.dao;

import java.util.List;

import net.iwsh.core.db.DBExecutor;
import net.iwsh.core.db.ext.DialectSyntaxParser;
import net.iwsh.core.db.ext.SQLWithParams;
import net.iwsh.core.exception.DBException;
import net.iwsh.core.utils.IDGenerator;

import com.us.usws.model.domain.base.BaseVideoCat;
import com.us.usws.model.flat.base.DOBaseVideoCat;

/**
 * DAO class
 * @author robot
 */
public class BaseVideoCatDAO {

	/**
	 * Query SQLs
	 */
	private static final String SQL_GET_TOTAL_COUNT = "select count(*) from hvb_video_cat";

	private static final String SQL_GET_ALL_BASEVIDEOCATS = "select * from hvb_video_cat";

	private static final String SQL_GET_BASEVIDEOCAT_BY_PK = "select * from hvb_video_cat where uid = ?";
	
	private static final String SQL_GET_RELATECAT_BY_TYPE = "select c.* from hv_video_type_cat_link l inner join hvb_video_cat c on l.video_cat_uid=c.uid where video_type_id = ?";

	/**
	 * get categories by video ID
	 */
	private static final String SQL_GET_CATS_BY_VIDEO_UID = "select c.* from hv_video_cat_link l inner join hvb_video_cat c on l.cat_uid=c.uid where l.video_uid=?";

	/**
	 * CUR SQLs
	 */
	private static final String SQL_INSERT_BASEVIDEOCAT = "insert into hvb_video_cat(uid,code,name,description,create_by_uid,create_date,last_upd_by_uid,last_upd_date) values (?,?,?,?,?,?,?,?)";

	private static final String SQL_UPDATE_BASEVIDEOCAT = "update hvb_video_cat set code=?, name=?, description=?, last_upd_by_uid=?, last_upd_date=? where uid=?";

	private static final String SQL_DEL_BASEVIDEOCAT = "delete from hvb_video_cat where uid=?";
	
	private static final String SQL_INSERT_RELATECAT = "insert into hv_video_type_cat_link(video_cat_uid, video_type_id) values(?, ?)";
	
	private static final String SQL_DEL_RELATECAT = "delete from hv_video_type_cat_link where video_type_id = ?"; 

	/**
	 * DBExeutor
	 */
	private DBExecutor dbe = new DBExecutor();

	/**
	 * Get Count.
	 */
	public int getTotalCount() {
		return dbe.getCount(SQL_GET_TOTAL_COUNT);
	}

	/**
	 * Get all list.
	 */
	public List<BaseVideoCat> getAllBaseVideoCats() {
		return dbe.list(SQL_GET_ALL_BASEVIDEOCATS, BaseVideoCat.class);
	}

	/**
	 * Get all list by pagination.
	 */
	public List<BaseVideoCat> getBaseVideoCats(int start, int end) {
		SQLWithParams swp = DialectSyntaxParser.getParser().getPaginationSQLWithParams(SQL_GET_ALL_BASEVIDEOCATS, null, start, end);
		return dbe.list(swp.getSql(), BaseVideoCat.class, swp.getParams());
	}

	/**
	 * Get BaseVideoCat by primary key.
	 */
	public BaseVideoCat getBaseVideoCat(String uid) {
		BaseVideoCat b = dbe.load(SQL_GET_BASEVIDEOCAT_BY_PK, BaseVideoCat.class, uid);
		return b;
	}

	/**
	 * get categories by video uid
	 * @param videoUid
	 * @return
	 */
	public List<BaseVideoCat> getCatsByVideo(String videoUid) {
		return dbe.list(SQL_GET_CATS_BY_VIDEO_UID, BaseVideoCat.class, videoUid);
	}
	
	/**
	 * get categories by video uid
	 * @param videoUid
	 * @return
	 */
	public List<BaseVideoCat> getRalateCatsByVideoType(int videoTypeId) {
		return dbe.list(SQL_GET_RELATECAT_BY_TYPE, BaseVideoCat.class, videoTypeId);
	}

	/**
	 * Insert one row.
	 */
	public String insertBaseVideoCat(DOBaseVideoCat baseVideoCat) {
		String uid = IDGenerator.getInstance().genUID();
		baseVideoCat.setUid(uid);
		dbe.execute(SQL_INSERT_BASEVIDEOCAT, baseVideoCat.getUid(), baseVideoCat.getCode(), baseVideoCat.getName(),
				baseVideoCat.getDescription(), baseVideoCat.getCreateByUid(), baseVideoCat.getCreateDate(), baseVideoCat.getLastUpdByUid(),
				baseVideoCat.getLastUpdDate());
		return uid;
	}
	
	/**
	 * insert related video 
	 * @param relateCatUid video cat uid
	 * @param videoTypeId
	 */
	public void insertRelatedVideoCat(List<String> relateCatUid, int videoTypeId) {
		
		dbe.startTransaction();
		try {
			dbe.executeBatch(SQL_DEL_RELATECAT, videoTypeId);
			
			for(String catUid: relateCatUid) {
				dbe.executeBatch(SQL_INSERT_RELATECAT, catUid, videoTypeId);
			}
		} catch (Exception e) {
			dbe.rollbackTransaction();
			e.printStackTrace();
			throw new DBException("excute failure!!");
		} finally {
			dbe.commitTransaction();
		}
		
	}

	/**
	 * Update by PK.
	 */
	public void updateBaseVideoCat(DOBaseVideoCat baseVideoCat) {
		dbe.execute(SQL_UPDATE_BASEVIDEOCAT, baseVideoCat.getCode(), baseVideoCat.getName(), baseVideoCat.getDescription(),
				baseVideoCat.getLastUpdByUid(), baseVideoCat.getLastUpdDate(),
				baseVideoCat.getUid());
	}

	/**
	 * Delete row by PK.
	 */
	public void deleteBaseVideoCat(String uid) {
		dbe.execute(SQL_DEL_BASEVIDEOCAT, uid);
	}
	
}